package com.biz.hunter.dao;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.biz.hunter.db.entity.JobDesc;
import com.biz.hunter.db.entity.JobSubscriber;
import com.biz.hunter.dbutil.BaseDAO;

@Repository
public class JobSubscriberDAO {
	public static final int PAGE_SIZE = 10;
	private static Logger logger = LoggerFactory.getLogger(JobSubscriberDAO.class);
private static final String SQL_GET_ALL = "select distinct job.id as id,job.jobcode jobcode,job.name as name ,"
		+ "u.openid as openid ,u.nickname as nickname "
			+ "from tb_jobdesc job, userinfo u, userJobs r"
+ " where r.openid = u.openid and job.id=r.jobid ";
	
	private static final String SQL_GET_ALL_COUNT = "select count(distinct r.openid ,r.jobid) "
			+ " from tb_jobdesc job, userinfo u, userJobs r"
+ " where r.openid = u.openid and job.id"
+ "= r.jobid ";
	private static final String SQL_GET = "select * from userJobs  where id = ?";
	

	public List<JobSubscriber> getALL() {
		return BaseDAO.queryForList(JobSubscriber.class, SQL_GET_ALL);
	}

	//
	// public Long getCountOfAll() {
	// return (Long) BaseDAO.queryOneRowOneCol(SQL_GET_ALL_COUNT);
	// }
	//
	// public List<JobDesc> getAllWithLimit(long start, long length) {
	// return BaseDAO.queryForList(JobDesc.class, SQL_GET_ALL_WITH_LIMIT, start,
	// length);
	// }

	public Long getCountOfAll(String name, Integer companyId) {
		String sql = SQL_GET_ALL_COUNT;
		if (StringUtils.hasText(name)) {
			sql += " and job.name like '%" + name + "%'";
		}
		if (companyId != null) {
			sql += " and companyId =" + companyId + " ";
		}
		
		return (Long) BaseDAO.queryOneRowOneCol(sql);
	}

	public List<JobSubscriber> getAllWithLimit(String name, Integer companyId,
			 long start, long length) {
		String sql = SQL_GET_ALL;
		if (StringUtils.hasText(name)) {
			sql += " and job.name like '%" + name + "%'";
		}
		if (companyId != null) {
			sql += " and companyId =" + companyId + " ";
		}
		
		sql += " LIMIT ?, ?";
		return BaseDAO.queryForList(JobSubscriber.class, sql, start, length);
	}

	public JobDesc getById(long jobId) {
		return BaseDAO.queryForObject(JobDesc.class, SQL_GET, jobId);
	}

	String SQL_GET_JOB_BY_NAME = "";
	public JobDesc getByJobname(String jobname) {
		if (StringUtils.isEmpty(jobname))
		{
			
		}
		return BaseDAO.queryForObject(JobDesc.class, SQL_GET_JOB_BY_NAME,
				jobname);
	}

	

	final String SQL_GET_PARAMS = "select * from userJobs where  ? limit ? ,?";

	public List<JobDesc> filterJobsPerPage(String param, int offset,
			int pageSize) {

		List<JobDesc> list = BaseDAO.queryForList(JobDesc.class,
				SQL_GET_PARAMS, param, offset, pageSize);

		return list;
	}
	
	final String SQL_like_job = "insert into userJobs(openid,jobid) values(?,?)";


	public void like(String openid, int jobId) {
		Long result = BaseDAO.save(SQL_like_job, openid, jobId);
	}
	final String SQL_GET_FAVORJOBS = "select t.* from userJobs t,userJobs j where t.id = j.jobid and openid = ?";

	public List<JobDesc> searchUserJobs(String openid) {
		List<JobDesc> list = BaseDAO.queryForList(JobDesc.class,
				SQL_GET_FAVORJOBS, openid);

		return list;
	}
}
